﻿using System;
using System.Data;

namespace WorkOrder.Business
{
    public class TaskReport
    {
        private DbHelper dbhelper = new DbHelper();

        #region 开通单总量

        public DataTable getSumOfNew(string startTime, string endTime)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number,A.product_id " +
                            "from table_work_order A,Table_Product B " +
                            "where A.product_id=B.product_id and A.order_type_id=1 and A.is_back=0 " +
                            "and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                            "group by B.product_name,A.product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 开通单有工程及时单

        public DataTable getHasProjectOfNew(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C ON A.order_id=C.order_id where "+
                            "A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=1 " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单') " +
                            "and C.request_time>=A.confirm_time "+
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=40" +
                            " ) A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 开通单有工程超时单

        public DataTable getHasProjectOfNewTimeout(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C ON A.order_id=C.order_id where " +
                            "A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=1 " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单') " +
                            "and C.request_time>=A.confirm_time " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>40" +
                            " ) A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 开通单无工程及时单

        public DataTable getHasNotProjectOfNew(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                "on A.product_id=B.product_id " +
                "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and " +
                "A.is_back=0 and A.order_type_id=1 and dbo.f_WorkDay(A.confirm_time,A.finish_time)<= 10 and " +
                "(A.product_id=1 or A.product_id=2 or A.product_id=4) and " +
                "A.order_id not in (select distinct order_id from table_order_record where " +
                "request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))) A " +
                "group by product_name,product_id;   " +
                "insert into #T select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=1 and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=7 and " +
                "(A.product_id<>1 and A.product_id<>2 and A.product_id<>4))A group by product_name,product_id;       " +
                "select * from #T;  drop table #T";

            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 开通单无工程超时单

        public DataTable getHasNotProjectOfNewTimeout(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                "on A.product_id=B.product_id " +
                "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and " +
                "A.is_back=0 and A.order_type_id=1 and dbo.f_WorkDay(A.confirm_time,A.finish_time)>10 and " +
                "(A.product_id=1 or A.product_id=2 or A.product_id=4) and " +
                "A.order_id not in (select distinct order_id from Table_Order_record where " +
                "request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))) A " +
                "group by product_name,product_id;   " +
                "insert into #T select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=1 and dbo.f_WorkDay(A.confirm_time,A.finish_time)>7 and " +
                "(A.product_id<>1 and A.product_id<>2 and A.product_id<>4))A group by product_name,product_id;       " +
                "select * from #T;  drop table #T";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 调整单总量

        public DataTable getSumOfModify(string startTime, string endTime)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number,A.product_id " +
                            "from table_work_order A,Table_Product B " +
                            "where A.product_id=B.product_id and A.order_type_id=3 and A.is_back=0 " +
                            "and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "group by B.product_name,A.product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 调整单有工程及时单

        public DataTable getHasProjectOfModify(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C on A.order_id=C.order_id where A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=3 " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=40 " +
                            "and C.request_time>=A.confirm_time " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单')) A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 调整单有工程超时单

        public DataTable getHasProjectOfModifyTimeout(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C on A.order_id=C.order_id where A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=3 " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>40 " +
                            "and C.request_time>=A.confirm_time " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单')) A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 调整单无工程及时单

        public DataTable getHasNotProjectOfModify(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                "on A.product_id=B.product_id where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and A.is_back=0 and A.order_type_id=3 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<= 10 " +
                "and A.order_id not in (select order_id from Table_Order_record C where " +
                "C.request_time>=A.confirm_time and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单'))) A " +
                "group by product_name,product_id;" +
                "insert into #T " +
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=3 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4)" +
                ") A " +
                "group by product_name,product_id;         " +
                "select * from #T;  drop table #T";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 调整单无工程超时单

        public DataTable getHasNotProjectOfModifyTimeout(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=3 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>10 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time "+
                "and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))) A " +
                "group by product_name,product_id;  " +
                "insert into #T " +
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=3 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4)" +
                ") A " +
                "group by product_name,product_id;         " +
                "select * from #T;  drop table #T";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 取消单总量

        public DataTable getSumOfCancel(string startTime, string endTime)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number,A.product_id " +
                            "from table_work_order A,Table_Product B " +
                            "where A.product_id=B.product_id and A.order_type_id=2 and A.is_back=0 " +
                            "and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "group by B.product_name,A.product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 取消单有工程及时单

        public DataTable getHasProjectOfCancel(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C on A.order_id=C.order_id where A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=2 " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=40 and C.request_time>=A.confirm_time " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单') " +
                            ") A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 取消单有工程超时单

        public DataTable getHasProjectOfCancelTimeout(string startTime, string endTime)
        {
            string sqlstr = "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                            "(select distinct B.product_name,A.order_id,A.product_id from table_work_order A inner join Table_Product B " +
                            "on A.product_id=B.product_id inner join Table_Order_record C on A.order_id=C.order_id where A.finish_time between '" + startTime + " 0:0:0' and '" +
                            endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.is_back=0 and A.order_type_id=2 " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>40 and C.request_time>=A.confirm_time " +
                            "and (C.process='传输电路调度单(新建)' or C.process='工程建设单' or C.process='工程反馈单') " +
                            ") A " +
                            "group by product_name,product_id";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 取消单无工程及时单

        public DataTable getHasNotProjectOfCancel(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=2 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<= 10 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))) A " +
                "group by product_name,product_id;    " +
                "insert into #T " +
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=2 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4)" +
                ") A " +
                "group by product_name,product_id;         " +
                "select * from #T;  drop table #T";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 取消单无工程超时单

        public DataTable getHasNotProjectOfCancelTimeout(string startTime, string endTime)
        {
            string sqlstr =
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id into #T from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=2 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>10 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))) A " +
                "group by product_name,product_id;    " +
                "insert into #T " +
                "select product_name,count(cast(order_id as varchar(40))) as number,product_id from " +
                "(select B.product_name,A.order_id,A.product_id from table_work_order A ,Table_Product B " +
                "where A.product_id=B.product_id and A.finish_time between '" + startTime + " 0:0:0' and '" + endTime +
                " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                "and A.is_back=0 and A.order_type_id=2 " +
                "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4)" +
                ") A " +
                //")A where A.order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                "group by product_name,product_id;         " +
                "select * from #T;  drop table #T";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 有工程及时单总计

        public int getSumHasEn(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "select count(cast(A.order_id as varchar(40))) as number from table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and A.is_back=0 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=40 and A.order_id in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }

        #endregion

        #region 有工程超时单总计

        public int getSumHasEnTimeout(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "select count(cast(A.order_id as varchar(40))) as number from table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and A.is_back=0 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>40 and A.order_id in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }

        #endregion

        #region 无工程及时单总计

        public int getSumHasNotEn(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "declare @tmp int; " +
                            "select @tmp=count(cast(A.order_id as varchar(40))) from table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                            "and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<= 10 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));        " +
                            "select @tmp=@tmp+count(cast(A.order_id as varchar(40))) from " +
                            "table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                            "and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)<=7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4); " +
                            //"and A.order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单');       " +
                            "select @tmp";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }

        #endregion

        #region 无工程超时单总计

        public int getSumHasNotEnTimeout(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "declare @tmp int;     " +
                            "select @tmp=count(cast(A.order_id as varchar(40))) from table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                            "and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>10 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                            "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));         " +
                            "select @tmp=@tmp+count(cast(A.order_id as varchar(40))) from " +
                            "table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                            "and A.is_back=0 and (A.order_status='已关闭' or A.order_status='待业务确认') " +
                            "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>7 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4); " +
                            //"and A.order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单');        " +
                            "select @tmp";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }

        #endregion

        #region 总计

        public int getSum(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "select count(cast(A.order_id as varchar(40))) as number from table_work_order A " +
                            "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                            "and A.is_back=0";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getSumList(string startTime, string endTime)
        {
            string sqlstr = "exec UP_GetSumFinishOrder '"+startTime+" 0:0:0','"+endTime+" 23:59:59','1=1'";
            return dbhelper.Query("", sqlstr);
        }
        public DataTable getSumList(string startTime, string endTime, string condition)
        {
            string sqlstr = "exec UP_GetSumFinishOrder '" + startTime + " 0:0:0','" + endTime + " 23:59:59'";
            if (!string.IsNullOrEmpty(condition)) sqlstr += ",'" + condition + "'";
            else sqlstr += ",'1=1'";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 已完成的超时工单数量
        /// <summary>
        /// 已完成的超时工单数量
        /// </summary>
        /// <param name="startTime">完成时间起</param>
        /// <param name="endTime">完成时间止</param>
        /// <returns></returns>
        public int getCountTimeOut(string startTime, string endTime)
        {
            int tmp = 0;
            string sqlstr = "";
            sqlstr += "declare @a int;";
            //--有工程超时
            sqlstr += "set @a=(select count(cast(order_id as varchar(40))) as number from table_work_order A " +
                      "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                      "and A.is_back=0 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                      "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>40 "+
                      "and A.order_id in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))" +
                      ");     ";
            //--专线无工程超时
            sqlstr += "set @a=@a+(select count(cast(order_id as varchar(40))) as number from " +
                      "table_work_order A " +
                      "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                      "and A.is_back=0 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                      "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>10 " +
                      "and A.order_id not in (select distinct order_id from Table_Order_record where request_time>=A.confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))" +
                      ");       ";
            //--非专线超时
            sqlstr += "set @a=@a+(select count(cast(order_id as varchar(40))) as number from " +
                      "table_work_order A " +
                      "where A.finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                      "and A.is_back=0 and (A.product_id<>1 and A.product_id<>2 and A.product_id<>4) " +
                      "and dbo.f_WorkDay(A.confirm_time,A.finish_time)>=7);";
            sqlstr += "select @a;";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getCountTimeOutList(string startTime, string endTime)
        {
            string sqlstr = "exec UP_GetSumFinishOrderTimeOut '" + startTime + " 0:0:0','" + endTime + " 23:59:59'";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 在线工单
        public int getOnline()
        {
            int tmp = 0;
            string sqlstr = "select count(cast(order_id as varchar(40))) as number from Table_Work_order where finish_time is null";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        #endregion

        #region 售中环节在线工单
        public int getConfirmOnLine()
        {
            int tmp = 0;
            string sqlstr =
                "select count(cast(order_id as varchar(40))) as number from Table_Work_order where finish_time is null and confirm_time is not null and confirm_time > '1950-1-1'";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getConfirmOnLineList(string condition )
        {
            string sqlstr = "";
            if (condition!="")
                sqlstr = "exec UP_GetConfirmOnlie '" + condition + "'";
            else
                sqlstr = "exec UP_GetConfirmOnlie '1=1'";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 售中环节在线工单超时
        public int getConfirmOnlineTimeOut()
        {
            int tmp = 0;
            string sqlstr = "";

            sqlstr += "DECLARE @a INT;";
            //--专线非取消超时
            sqlstr +=
                "set @a=(select count(cast(A.order_id as varchar(40))) as number from Table_Work_order A " +
                "where A.finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.order_type_id<>2 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=40);     ";
            //--专线取消超时
            sqlstr +=
                "set @a=@a+(select count(cast(A.order_id as varchar(40))) as number from table_work_order A " +
                "where A.finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.order_type_id=2 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=10);    ";
            //--非专线超时
            sqlstr +=
                "set @a=@a+(select count(cast(A.order_id as varchar(40))) as number from Table_Work_order A " +
                "where A.finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.product_id<>1 and A.product_id<>2 and A.product_id<>4 " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=7);     ";
            sqlstr += "    select @a;";

            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getConfirmOnlineTimeOutList()
        {
            string sqlstr =
                   "exec UP_GetConfirmOnlieTimeOut";
            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 售中环节在线工单预警
        #region 专线30天
        public int getConfirmOnlineAlarm30()
        {
            int tmp = 0;
            //--专线非取消预警
            string sqlstr =
                "DECLARE @a INT;set @a=(select count(cast(A.order_id as varchar(40))) as number from Table_Work_order A " +
                "where finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.order_type_id<>2 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=30);       ";
            sqlstr += "    select @a;";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getConfirmOnlineAlarm30List()
        {
            string sqlstr =
                   "exec UP_GetConfirmOnlineAlarm30";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 非专线5天
        public int getConfirmOnlineAlarm5()
        {
            int tmp = 0;
            //--非专线预警
            string sqlstr =
                "select count(cast(A.order_id as varchar(40))) as number from Table_Work_order A " +
                "where A.finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.product_id<>1 and A.product_id<>2 and A.product_id<>4 " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=5;";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getConfirmOnlineAlarm5List()
        {
            string sqlstr =
                   "exec UP_GetConfirmOnlineAlarm5";
            return dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 专线取消单10天
        public int getConfirmOnlineAlarm10()
        {
            int tmp = 0;
            //--专线取消预警
            string sqlstr =
                "select count(cast(A.order_id as varchar(40))) as number from table_work_order A " +
                "where A.finish_time is null and A.confirm_time is not null and A.confirm_time > '1950-1-1' " +
                "and A.order_type_id=2 and (A.product_id=1 or A.product_id=2 or A.product_id=4) " +
                "and dbo.f_WorkDay(A.confirm_time,GETDATE())>=10;";
            object obj = dbhelper.ExecuteScalar(sqlstr);
            if (!obj.Equals(DBNull.Value))
                try
                {
                    tmp = Convert.ToInt32(obj);
                }
                catch
                {
                    tmp = 0;
                }
            return tmp;
        }
        public DataTable getConfirmOnlineAlarm10List()
        {
            string sqlstr =
                   "exec UP_GetConfirmOnlineAlarm10";
            return dbhelper.Query("", sqlstr);
        }
        #endregion
        #endregion

        #region 工单列表

        /// <summary>
        /// 
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="orderType">工单类型,1开通,3调整,2取消</param>
        /// <param name="product">产品类型</param>
        /// <param name="timeOut">是否及时,0否,1是</param>
        /// <param name="timeOutValue">超时标准</param>
        /// <param name="hasE">是否有工程,0否,1是</param>
        /// <returns></returns>
        public DataTable getOrderList(string startTime, string endTime, string orderType, string product, string timeOut,
                                      string timeOutValue, string hasE)
        {
            string sqlstr = "";

            #region 开通单 有工程 及时

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and (product_id=1 or product_id=2 or product_id=4) and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') and " +
                         "is_back=0 and order_type_id=1 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单')) ";

            #endregion

            #region 开通单 有工程 及时 总计

            if (orderType == "1" && product == "C" && timeOut == "1" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where (product_id=1 or product_id=2 or product_id=4) " +
                         "and finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and  " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 开通单 无工程 及时

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=1 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));          " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=1 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 开通单 无工程 及时 总计

            if (orderType == "1" && product == "C" && timeOut == "1" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));          " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 开通单 有工程 超时

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=1 and (product_id=1 or product_id=2 or product_id=4) and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 开通单 有工程 超时 总计

            if (orderType == "1" && product == "C" && timeOut == "0" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where (product_id=1 or product_id=2 or product_id=4) and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 开通单 无工程 超时

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=1 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));       " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=1 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 开通单 无工程 超时 总计

            if (orderType == "1" && product == "C" && timeOut == "0" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));    " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=1 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 开通单 总量

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "2" &&
                timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') and " +
                         "is_back=0 and order_type_id=1";

            #endregion

            #region 开通单 总量 总计

            if (orderType == "1" && product == "C" && timeOut == "2" && timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=1";

            #endregion

            #region 调整单 有工程 及时

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (product_id=1 or product_id=2 or product_id=4) and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and  " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 调整单 有工程 及时 总计

            if (orderType == "3" && product == "C" && timeOut == "1" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and (product_id=1 or product_id=2 or product_id=4) and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 调整单 无工程 及时

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));           " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=3 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 调整单 无工程 及时 总计

            if (orderType == "3" && product == "C" && timeOut == "1" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=3 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));     " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=3 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 调整单 有工程 超时

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "(product_id=1 or product_id=2 or product_id=4) and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and  " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 调整单 有工程 超时 总计

            if (orderType == "3" && product == "C" && timeOut == "0" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "(product_id=1 or product_id=2 or product_id=4) and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 调整单 无工程 超时

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=3 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));     " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=3 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 调整单 无工程 超时 总计

            if (orderType == "3" && product == "C" && timeOut == "0" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=3 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));      " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and order_type_id=3 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 调整单 总量

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "2" &&
                timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3";

            #endregion

            #region 调整单 总量 总计

            if (orderType == "3" && product == "C" && timeOut == "2" && timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=3";

            #endregion

            #region 取消单 有工程 及时

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "order_type_id=2 and (order_status='已关闭' or order_status='待业务确认') and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and " +
                         "(product_id=1 or product_id=2 or product_id=4) and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 取消单 有工程 及时 总计

            if (orderType == "2" && product == "C" && timeOut == "1" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and (product_id=1 or product_id=2 or product_id=4) and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 取消单 无工程 及时

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "1" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));     " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 取消单 无工程 及时 总计

            if (orderType == "2" && product == "C" && timeOut == "1" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<= 10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));        " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 取消单 有工程 超时

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 and (product_id=1 or product_id=2 or product_id=4) and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 取消单 有工程 超时 总计

            if (orderType == "2" && product == "C" && timeOut == "0" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where (product_id=1 or product_id=2 or product_id=4) and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 取消单 无工程 超时

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "0" &&
                timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单')); " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and finish_time between '" + startTime + " 0:0:0' and '" +
                         endTime + " 23:59:59' and (order_status='已关闭' or order_status='待业务确认') " +
                         "and is_back=0 and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 取消单 无工程 超时 总计

            if (orderType == "2" && product == "C" && timeOut == "0" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单')); " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2 " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 取消单 总量

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "2" &&
                timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2";

            #endregion

            #region 取消单 总量 总计

            if (orderType == "2" && product == "C" && timeOut == "2" && timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and order_type_id=2";

            #endregion

            #region 总计 有工程 及时

            if (orderType == "A" && product == "C" && timeOut == "1" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and (product_id=1 or product_id=2 or product_id=4) and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)<= 40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 总计 有工程 超时

            if (orderType == "A" && product == "C" && timeOut == "0" && timeOutValue == "40" && hasE == "1")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') and (product_id=1 or product_id=2 or product_id=4) and " +
                         "dbo.f_WorkDay(confirm_time,finish_time)>40 and " +
                         "order_id in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'))";

            #endregion

            #region 总计 无工程 及时

            if (orderType == "A" && product == "C" && timeOut == "1" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单'));    " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)<=7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 总计 无工程 超时

            if (orderType == "A" && product == "C" && timeOut == "0" && timeOutValue == "10$7" && hasE == "0")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 into #T from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>10 and (product_id=1 or product_id=2 or product_id=4) " +
                         "and order_id not in (select distinct order_id from Table_Order_record where request_time>=confirm_time and (process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单')); " +
                         "insert into #T " +
                         "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' " +
                         "and is_back=0 and (order_status='已关闭' or order_status='待业务确认') " +
                         "and dbo.f_WorkDay(confirm_time,finish_time)>7 and (product_id<>1 and product_id<>2 and product_id<>4) " +
                         //"and order_id not in (select distinct order_id from Table_Order_record where process='传输电路调度单(新建)' or process='工程建设单' or process='工程反馈单') " +
                         ";        " +
                         "select * from #T;  drop table #T";

            #endregion

            #region 总计 总量

            if (orderType == "A" && product == "C" && timeOut == "2" && timeOutValue == "" && hasE == "2")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认') ";

            #endregion

            #region 平均历时 开通

            if (orderType == "1" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "3" &&
                timeOutValue == "" && hasE == "3")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where order_type_id=1 and product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认')";

            #endregion

            #region 平均历时 调整

            if (orderType == "3" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "3" &&
                timeOutValue == "" && hasE == "3")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where product_id=" + product + " and order_type_id=3 and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认')";

            #endregion

            #region 平均历时 取消

            if (orderType == "2" && !string.IsNullOrEmpty(product) && product != "C" && timeOut == "3" &&
                timeOutValue == "" && hasE == "3")
                sqlstr = "select order_id,order_number as 工单号,title as 工单主题 from table_work_order " +
                         "where order_type_id=2 and product_id=" + product + " and " +
                         "finish_time between '" + startTime + " 0:0:0' and '" + endTime + " 23:59:59' and " +
                         "is_back=0 and (order_status='已关闭' or order_status='待业务确认')";

            #endregion

            return dbhelper.Query("", sqlstr);
        }

        #endregion

        #region 返回主单状态
        public System.Data.DataSet GetStatus()
        {
            string sqlstr = "select order_status from Table_Order_status";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回镇区资料
        public System.Data.DataSet GetTown()
        {
            string sqlstr = "select town_id,town_name from Table_Town";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回区域资料
        public System.Data.DataSet GetArea()
        {
            string sqlstr = "select area_id,area_name from Table_Area";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回子单
        public System.Data.DataSet GetProcess()
        {
            string sqlstr = "select process from Table_Process";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回产品类别资料
        public System.Data.DataSet GetProduct()
        {
            string sqlstr = "select product_id,product_name from Table_Product";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回工单类型资料
        public System.Data.DataSet GetOrderType()
        {
            string sqlstr = "select order_type_id,order_type from Table_Order_type";
            return dbhelper.Query(sqlstr);
        }
        #endregion
    }
}
